const { pool } = require('../config/database');

class Refrigerator {
  // 获取所有冰箱
  static async getAllRefrigerators() {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, location, brand, model, capacity, temperature_control,
               status, purchase_date, warranty_until, notes, created_at, updated_at 
        FROM refrigerators 
        ORDER BY location ASC, name ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取冰箱列表失败:', error.message);
      throw error;
    }
  }

  // 根据位置获取冰箱
  static async getRefrigeratorsByLocation(location) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, location, brand, model, capacity, temperature_control,
               status, purchase_date, warranty_until, notes, created_at, updated_at 
        FROM refrigerators 
        WHERE location = ?
        ORDER BY name ASC
      `, [location]);
      return rows;
    } catch (error) {
      console.error('根据位置获取冰箱失败:', error.message);
      throw error;
    }
  }

  // 获取活跃状态的冰箱
  static async getActiveRefrigerators() {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, location, brand, model, capacity, temperature_control,
               status, purchase_date, warranty_until, notes, created_at, updated_at 
        FROM refrigerators 
        WHERE status = 'active'
        ORDER BY location ASC, name ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取活跃冰箱失败:', error.message);
      throw error;
    }
  }

  // 根据ID获取冰箱详情
  static async getRefrigeratorById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, location, brand, model, capacity, temperature_control,
               status, purchase_date, warranty_until, notes, created_at, updated_at 
        FROM refrigerators 
        WHERE id = ?
      `, [id]);

      if (rows.length === 0) return null;
      return rows[0];
    } catch (error) {
      console.error('获取冰箱详情失败:', error.message);
      throw error;
    }
  }

  // 获取冰箱中的食物数量
  static async getRefrigeratorFoodCount(refrigeratorId) {
    try {
      const [rows] = await pool.execute(`
        SELECT 
          COUNT(*) as total_count,
          SUM(CASE WHEN temperature_status = 'room_temp' THEN 1 ELSE 0 END) as room_temp_count,
          SUM(CASE WHEN temperature_status = 'refrigerated' THEN 1 ELSE 0 END) as refrigerated_count,
          SUM(CASE WHEN temperature_status = 'frozen' THEN 1 ELSE 0 END) as frozen_count
        FROM foods 
        WHERE refrigerator_id = ?
      `, [refrigeratorId]);
      
      return rows[0] || { total_count: 0, room_temp_count: 0, refrigerated_count: 0, frozen_count: 0 };
    } catch (error) {
      console.error('获取冰箱食物数量失败:', error.message);
      throw error;
    }
  }

  // 获取冰箱详情（包含食物统计）
  static async getRefrigeratorWithStats(id) {
    try {
      const refrigerator = await this.getRefrigeratorById(id);
      if (!refrigerator) return null;

      const foodStats = await this.getRefrigeratorFoodCount(id);
      
      return {
        ...refrigerator,
        food_stats: foodStats
      };
    } catch (error) {
      console.error('获取冰箱详情和统计失败:', error.message);
      throw error;
    }
  }

  // 创建冰箱
  static async createRefrigerator(refrigeratorData) {
    try {
      const { 
        name, location, brand, model, capacity, temperature_control, 
        status, purchase_date, warranty_until, notes 
      } = refrigeratorData;
      
      const [result] = await pool.execute(`
        INSERT INTO refrigerators (name, location, brand, model, capacity, 
                                 temperature_control, status, purchase_date, 
                                 warranty_until, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [name, location, brand, model, capacity, temperature_control, 
          status, purchase_date, warranty_until, notes]);

      return {
        id: result.insertId,
        ...refrigeratorData
      };
    } catch (error) {
      console.error('创建冰箱失败:', error.message);
      throw error;
    }
  }

  // 更新冰箱
  static async updateRefrigerator(id, refrigeratorData) {
    try {
      const { 
        name, location, brand, model, capacity, temperature_control, 
        status, purchase_date, warranty_until, notes 
      } = refrigeratorData;
      
      await pool.execute(`
        UPDATE refrigerators 
        SET name = ?, location = ?, brand = ?, model = ?, capacity = ?, 
            temperature_control = ?, status = ?, purchase_date = ?, 
            warranty_until = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [name, location, brand, model, capacity, temperature_control, 
          status, purchase_date, warranty_until, notes, id]);

      return await this.getRefrigeratorById(id);
    } catch (error) {
      console.error('更新冰箱失败:', error.message);
      throw error;
    }
  }

  // 删除冰箱
  static async deleteRefrigerator(id) {
    try {
      // 检查是否有食物关联到此冰箱
      const [foodCheck] = await pool.execute(`
        SELECT COUNT(*) as count FROM foods WHERE refrigerator_id = ?
      `, [id]);

      if (foodCheck[0].count > 0) {
        throw new Error('无法删除冰箱，仍有食物存储在此冰箱中');
      }

      const [result] = await pool.execute(`
        DELETE FROM refrigerators WHERE id = ?
      `, [id]);

      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除冰箱失败:', error.message);
      throw error;
    }
  }

  // 搜索冰箱
  static async searchRefrigerators(keyword) {
    try {
      const [rows] = await pool.execute(`
        SELECT id, name, location, brand, model, capacity, temperature_control,
               status, purchase_date, warranty_until, notes, created_at, updated_at 
        FROM refrigerators 
        WHERE name LIKE ? OR location LIKE ? OR brand LIKE ? OR model LIKE ?
        ORDER BY location ASC, name ASC
      `, [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`, `%${keyword}%`]);
      return rows;
    } catch (error) {
      console.error('搜索冰箱失败:', error.message);
      throw error;
    }
  }

  // 获取冰箱统计信息
  static async getRefrigeratorStats() {
    try {
      const [totalCount] = await pool.execute(`
        SELECT COUNT(*) as total FROM refrigerators
      `);
      
      const [activeCount] = await pool.execute(`
        SELECT COUNT(*) as active FROM refrigerators WHERE status = 'active'
      `);
      
      const [locationStats] = await pool.execute(`
        SELECT location, COUNT(*) as count 
        FROM refrigerators 
        GROUP BY location 
        ORDER BY count DESC
      `);

      const [brandStats] = await pool.execute(`
        SELECT brand, COUNT(*) as count 
        FROM refrigerators 
        GROUP BY brand 
        ORDER BY count DESC
      `);

      return {
        total: totalCount[0].total,
        active: activeCount[0].active,
        by_location: locationStats,
        by_brand: brandStats
      };
    } catch (error) {
      console.error('获取冰箱统计失败:', error.message);
      throw error;
    }
  }
}

module.exports = Refrigerator;
